-- **************************************************************************
--    Project Name:   机器人推送小时车辆数据统计表
--    Job Name:
--    Description :
--    Author :        王圣雅
--    date：          2023/05/04
-- **************************************************************************
--    Relation ：
--    jms_hi__dm_oper_center_ship_summary_dt << [
--    jms_dwd__dwd_tmsnew_shipment_union_base_hi,
--    jms_dwd__dwd_tab_barscan_send_base_hi,
--    jms_dwd__dwd_tab_barscan_arrival_base_hi,
--    jms_dwd__dwd_tmsnew_shipment_stop_union_base_hi
--]
-- **************************************************************************
--
--
-- **************************************************************************

insert overwrite table jms_tmp.dm_oper_center_ship_detail_dt
select send.waybill_no                                --运单号
     , send.shipment_no                               --发件任务号
     , send.next_site_code                            --发件任务号
     , stop.shipment_no                               --任务号
     , ship.shipment_state                            --任务号
     , stop.sort_num                                  --任务号
     , stop.center_code                               --站点编码
     , stop.center_name                               --站点名称
     , arrival.waybill_no                             --站点名称
     , arrival.shipment_no                            --站点名称
     , stop.planned_arrival_time
     , stop.planned_departure_time
     , stop.actual_arrival_time
     , stop.actual_departure_time
     , stop.unlock_time
     , case
           when to_date(stop.planned_arrival_time) = '{{ execution_date | cst_ds }}' and stop.unlock_time is null and
                stop.actual_arrival_time is null
               then stop.shipment_no end as zt_car    --是否在途车辆,限制为在途且限制计划到车时间是今天
     , case
           when to_date(stop.planned_arrival_time) = '{{ execution_date | cst_ds }}' and stop.unlock_time is null and
                stop.actual_arrival_time is null
               then send.waybill_no end  as zt_piece  --是否在途车辆,限制为在途且限制计划到车时间是今天
     , case
           when (to_date(stop.unlock_time) = '{{ execution_date | cst_ds }}' or
                 to_date(stop.actual_arrival_time) = '{{ execution_date | cst_ds }}')
               and send.waybill_no is not null and arrival.waybill_no is not null
               then send.waybill_no end  as yx_piece  --已卸,解锁/解车时间 或 实际到达/司机签到时间在当天
     , case
           when to_date(stop.unlock_time) = '{{ execution_date | cst_ds }}' or
                to_date(stop.actual_arrival_time) = '{{ execution_date | cst_ds }}'
               then send.waybill_no end  as all_piece --所有 解锁/解车时间 或 实际到达/司机签到时间在当天
     , to_date(create_time)              as create_date
     , '{{ execution_date | cst_hour }}'                   as stat_time
     , '{{ execution_date | cst_ds }}'                      as dt
from (select stop.*
      from (select shipment_no
                 , sort_num
                 , network_code as center_code
                 , network_name as center_name
                 , create_time
                 , unlock_time
                 , planned_arrival_time
                 , planned_departure_time
                 , actual_arrival_time
                 , actual_departure_time
                 , update_time
                 , clock_time
            from jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi --干线任务表
            where substr(dt, 1, 10) >= '{{ execution_date | cst_ds }}'
              and table_type = 1 --限制为干线
              and sort_num > 1) stop
               left join jms_dim.dim_network_whole_massage dim
                         on dim.code = stop.center_code
      where dim.network_type = 4) stop
         inner join (select shipment_no    --任务号
                          , shipment_state --任务号状态:待调度0 已调度1 待执行2 在途3 已完成4 已作废5
                          , update_time
                     from jms_dwd.dwd_tmsnew_shipment_union_base_hi
                     where substr(dt, 1, 10) >= '{{ execution_date | cst_ds }}'
                       and table_type = 1
                       and shipment_state in (3, 4)) ship on ship.shipment_no = stop.shipment_no
         left join (select *
                    from (select waybill_no        as waybill_no
                               , scan_site_code    as scan_site_code
                               , input_site        as scan_site_name
                               , next_station_code as next_site_code
                               , shipment_no       as shipment_no
                          from jms_dwd.dwd_tab_barscan_send_base_hi
                          where substr(dt, 1, 10) between date_sub('{{ execution_date | cst_ds }}', 10) and '{{ execution_date | cst_ds }}'
                            and nvl(shipment_no, '99999') not like 'ZX%') a) send
                   on send.next_site_code = stop.center_code
                       and send.shipment_no = stop.shipment_no
         left join (select *
                    from (select dt
                               , waybill_no     as waybill_no
                               , scan_site_code as scan_site_code
                               , scan_site      as scan_site_name
                               , transfer_code  as shipment_no
                          from jms_dwd.dwd_tab_barscan_arrival_base_hi
                          where substr(dt, 1, 10) between date_sub('{{ execution_date | cst_ds }}', 10) and '{{ execution_date | cst_ds }}'
                            and nvl(transfer_code, '99999') not like 'ZX%') a) arrival
                   on send.waybill_no = arrival.waybill_no
                       and send.next_site_code = arrival.scan_site_code
                        distribute by dt, PMOD(HASH(RAND()), 30);
                       
insert overwrite table jms_dm.dm_oper_center_ship_summary_dt
select stat_time                    as stat_time      --统计时间
     , center_code                  as center_code    --中心编码
     , center_name                  as center_name    --中心名称
     , count(distinct ydwx_car_cnt) as ydwx_car_cnt   --已到未卸车辆
     , sum(ydwx_piece_cnt)          as ydwx_piece_cnt --已到未卸件辆
     , count(distinct yx_car_cnt)   as yx_car_cnt     --已卸车辆
     , sum(yx_piece_cnt)            as yx_piece_cnt   --已卸件辆
     , sum(zt_car_cnt)              as zt_car_cnt     --在途车辆
     , sum(zt_piece_cnt)            as zt_piece_cnt   --在途件辆
     , dt
from (select stat_time                                                                  as stat_time      --统计时间
           , shipment_no                                                                as shipment_no    --任务号
           , center_code                                                                as center_code    --站点编码
           , center_name                                                                as center_name    --站点名称
           , zt_car                                                                     as zt_car_cnt     --在途车辆
           , zt_cnt                                                                     as zt_piece_cnt   --在途件量
           , case when (yx_cnt < all_cnt * 0.8) and is_flag = 0 then shipment_no end    as ydwx_car_cnt   --已到未卸车辆
           , case when (yx_cnt < all_cnt * 0.8) and is_flag = 0 then all_cnt else 0 end as ydwx_piece_cnt --已到未卸件量
           , case
                 when is_flag = 1 then shipment_no
                 when (yx_cnt > all_cnt * 0.8) then shipment_no end                     as yx_car_cnt     --已卸车辆
           , case
                 when is_flag = 1 then all_cnt
                 when (yx_cnt > all_cnt * 0.8) then yx_cnt
                 else 0 end                                                             as yx_piece_cnt   --已卸件量
           , yx_cnt
           , all_cnt                                                                    as all_cnt        --总件量
           , dt                                                                         as dt             --分区日期
      from (select stat_time                                   as stat_time   --统计时间
                 , shipment_no                                 as shipment_no --任务号
                 , center_code                                 as center_code --站点编码
                 , center_name                                 as center_name --站点名称
                 , if(actual_departure_time is not null, 1, 0) as is_flag     --是否离开
                 , count(distinct zt_car)                      as zt_car      --在途车量
                 , count(distinct zt_piece)                    as zt_cnt      --在途件量
                 , count(distinct yx_piece)                    as yx_cnt      --已卸件量
                 , count(distinct all_piece)                   as all_cnt     --总件量
                 , dt                                          as dt          --分区日期
            from  jms_tmp.dm_oper_center_ship_detail_dt
            where dt = '{{ execution_date | cst_ds }}'
            group by stat_time   --统计时间
                   , shipment_no --任务号
                   , center_code --站点编码
                   , center_name --站点名称
                   , actual_departure_time
                   , dt --分区日期
           ) a) a
group by stat_time   --统计时间
       , center_code --站点编码
       , center_name --站点名称
       , dt distribute by dt, 1;